package com.moshang.exportsql.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@RestController
public class ReplaceUidController {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @GetMapping("/exportUid")
    public void getAllQueryResults() {
        List<Map<String, Object>> mlServiceIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT `obj_id`,`class_name`,date_format(create_at,'%Y-%m-%d') AS `year_month`,create_at AS `creation_date`,`last_update`,`recycled`,`container`,'' AS `display_class_name`,`create_at` FROM levault.ml_service", new MapSqlParameterSource());
        List<Map<String, Object>> mlClassIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT  `obj_id`,   `class_name`, date_format(create_at,'%Y-%m-%d') AS `year_month`,  create_at AS `creation_date`,   `last_update`,  `recycled`, `container`, '' AS `display_class_name`, `create_at` FROM  levault.ml_class", new MapSqlParameterSource());
        List<Map<String, Object>> clsAttrIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT `obj_id`,`class_name`,date_format(create_at,'%Y-%m-%d') AS `year_month`,create_at AS `creation_date`,`last_update`,`recycled`,`container`,'' AS `display_class_name`,`create_at` FROM cls_attr WHERE container ='Levault';", new MapSqlParameterSource());
        List<Map<String, Object>> clsMsgIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM cls_msg WHERE container ='Levault');", new MapSqlParameterSource());
        List<Map<String, Object>> clsConstIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM cls_const WHERE left_id IN(SELECT obj_id FROM ml_class WHERE container ='Levault'));", new MapSqlParameterSource());
        List<Map<String, Object>> mlAttributeIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT    `obj_id`,   `class_name`,   date_format(create_at,'%Y-%m-%d') AS `year_month`,    create_at AS `creation_date`,   `last_update`,  `recycled`,   `container`, '' AS `display_class_name`,   `create_at`FROM    levault.ml_attribute", new MapSqlParameterSource());
        List<Map<String, Object>> mlMessageIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM ml_message WHERE obj_id IN(SELECT right_id FROM cls_msg WHERE left_id IN(SELECT obj_id FROM ml_class WHERE container ='Levault')));", new MapSqlParameterSource());
        List<Map<String, Object>> mlConstantIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM ml_constant WHERE obj_id IN(SELECT right_id FROM cls_const WHERE left_id IN(SELECT obj_id FROM ml_class WHERE container ='Levault')));", new MapSqlParameterSource());
        List<Map<String, Object>> mlRuleIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM ml_rule WHERE code IN(SELECT rule_code FROM cls_attr WHERE left_id IN(SELECT obj_id FROM ml_class WHERE container ='Levault') and rule_code !='' and rule_code is not null));", new MapSqlParameterSource());
        List<Map<String, Object>> mlDbInfoIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM ml_db_info WHERE obj_id IN(SELECT right_id FROM svr_db WHERE left_id IN(SELECT obj_id FROM ml_service WHERE container ='Levault')));", new MapSqlParameterSource());
        List<Map<String, Object>> svrDbIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM svr_db WHERE left_id IN(SELECT obj_id FROM ml_service WHERE container ='Levault'));", new MapSqlParameterSource());
        List<Map<String, Object>> mlLangIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT obj_id,class_name,date_format(create_at,'%Y-%m-%d') AS `year_month`,create_at AS `creation_date`,`last_update`,`recycled`,`container`,'' AS `display_class_name`,`create_at` FROM ml_lang  WHERE container = 'Levault';", new MapSqlParameterSource());
        List<Map<String, Object>> mlCodeRepoIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM ml_code_repo);", new MapSqlParameterSource());
        List<Map<String, Object>>  attrLovIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM attr_lov WHERE left_id in(SELECT obj_id FROM ml_attribute WHERE obj_id IN(SELECT right_id FROM cls_attr WHERE left_id IN(SELECT obj_id FROM ml_class WHERE container ='Levault'))));", new MapSqlParameterSource());
        List<Map<String, Object>>  attrCodeIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM attr_code WHERE left_id in(SELECT obj_id FROM ml_attribute WHERE obj_id IN(SELECT right_id FROM cls_attr WHERE left_id IN(SELECT obj_id FROM ml_class WHERE container ='Levault'))));", new MapSqlParameterSource());

        mlServiceIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlClassIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        clsAttrIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        clsMsgIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        clsConstIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlAttributeIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlMessageIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlConstantIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlRuleIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlDbInfoIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        svrDbIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));

        mlLangIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        mlCodeRepoIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        attrLovIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        attrCodeIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));

        List<Map<String, Object>> combinedQueryResult = new ArrayList<>();
        combinedQueryResult.addAll(mlServiceIdQueryResult);
        combinedQueryResult.addAll(mlClassIdQueryResult);
        combinedQueryResult.addAll(clsAttrIdQueryResult);
        combinedQueryResult.addAll(clsMsgIdQueryResult);
        combinedQueryResult.addAll(clsConstIdQueryResult);
        combinedQueryResult.addAll(mlAttributeIdQueryResult);
        combinedQueryResult.addAll(mlMessageIdQueryResult);
        combinedQueryResult.addAll(mlConstantIdQueryResult);
        combinedQueryResult.addAll(mlRuleIdQueryResult);
        combinedQueryResult.addAll(mlDbInfoIdQueryResult);
        combinedQueryResult.addAll(svrDbIdQueryResult);
        combinedQueryResult.addAll(mlLangIdQueryResult);
        combinedQueryResult.addAll(mlCodeRepoIdQueryResult);
        combinedQueryResult.addAll(attrLovIdQueryResult);
        combinedQueryResult.addAll(attrCodeIdQueryResult);
        exportSqlScript(combinedQueryResult, "6智能体厂uid");

    }

    @GetMapping("/exportLovAndCode")
    public void exportLovAndCode() {
        List<Map<String, Object>> lovDefQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM levault.lov_def where container in('LEZAOBASEAPP','ProductDataManagement') and tenant_id='0';", new MapSqlParameterSource());
        List<Map<String, Object>> dynLovQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM levault.dyn_lov where `lov_code` in (SELECT code FROM levault.lov_def where container in('LEZAOBASEAPP','ProductDataManagement'));", new MapSqlParameterSource());
        List<Map<String, Object>> staticLovQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM levault.static_lov where `lov_code` in (SELECT code FROM levault.lov_def where container in('LEZAOBASEAPP','ProductDataManagement'));", new MapSqlParameterSource());
        List<Map<String, Object>> lovDefIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM levault.lov_def where container in('LEZAOBASEAPP','ProductDataManagement'));", new MapSqlParameterSource());
        List<Map<String, Object>> dynLovIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM levault.dyn_lov where `lov_code` in (SELECT code FROM levault.lov_def where container in('LEZAOBASEAPP','ProductDataManagement')));", new MapSqlParameterSource());
        List<Map<String, Object>> staticLovIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM levault.static_lov where `lov_code` in (SELECT code FROM levault.lov_def where container in('LEZAOBASEAPP','ProductDataManagement')));", new MapSqlParameterSource());


        List<Map<String, Object>> lovCombinedQueryResult = new ArrayList<>();
        lovDefQueryResult.forEach(map -> map.put("itable_name", "lov_def"));
        dynLovQueryResult.forEach(map -> map.put("itable_name", "dyn_lov"));
        staticLovQueryResult.forEach(map -> map.put("itable_name", "static_lov"));
        lovDefIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        dynLovIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        staticLovIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));



        lovCombinedQueryResult.addAll(lovDefQueryResult);
        lovCombinedQueryResult.addAll(dynLovQueryResult);
        lovCombinedQueryResult.addAll(staticLovQueryResult);
        lovCombinedQueryResult.addAll(lovDefIdQueryResult);
        lovCombinedQueryResult.addAll(dynLovIdQueryResult);
        lovCombinedQueryResult.addAll(staticLovIdQueryResult);
        // 生成动态列的SQL脚本并导出为文件
        exportSqlScript(lovCombinedQueryResult, "lov");


        List<Map<String, Object>> codeDefQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM levault.code_def where container in('LEZAOBASEAPP','ProductDataManagement');", new MapSqlParameterSource());
        List<Map<String, Object>> codeSegQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM levault.code_seg where `encode` in (SELECT code FROM levault.code_def where container in('LEZAOBASEAPP','ProductDataManagement'));", new MapSqlParameterSource());
        List<Map<String, Object>> codeValQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM levault.code_val where `encode` in (SELECT code FROM levault.code_def where container in('LEZAOBASEAPP','ProductDataManagement'));", new MapSqlParameterSource());
        List<Map<String, Object>> codeDefIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM levault.code_def where container in('LEZAOBASEAPP','ProductDataManagement'));", new MapSqlParameterSource());
        List<Map<String, Object>> codeSegIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM levault.code_seg where `encode` in (SELECT code FROM levault.code_def where container in('LEZAOBASEAPP','ProductDataManagement')));", new MapSqlParameterSource());
        List<Map<String, Object>> codeValIdQueryResult = namedParameterJdbcTemplate.queryForList("SELECT * FROM unique_id where obj_id in(SELECT obj_id FROM levault.code_val where `encode` in (SELECT code FROM levault.code_def where container in('LEZAOBASEAPP','ProductDataManagement')));", new MapSqlParameterSource());


        List<Map<String, Object>> encCodeCombinedQueryResult = new ArrayList<>();
        codeDefQueryResult.forEach(map -> map.put("itable_name", "code_def"));
        codeSegQueryResult.forEach(map -> map.put("itable_name", "code_seg"));
        codeValQueryResult.forEach(map -> map.put("itable_name", "code_val"));
        codeDefIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        codeSegIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));
        codeValIdQueryResult.forEach(map -> map.put("itable_name", "unique_id"));



        encCodeCombinedQueryResult.addAll(codeDefQueryResult);
        encCodeCombinedQueryResult.addAll(codeSegQueryResult);
        encCodeCombinedQueryResult.addAll(codeValQueryResult);
        encCodeCombinedQueryResult.addAll(codeDefIdQueryResult);
        encCodeCombinedQueryResult.addAll(codeSegIdQueryResult);
        encCodeCombinedQueryResult.addAll(codeValIdQueryResult);
        // 生成动态列的SQL脚本并导出为文件
        exportSqlScript(encCodeCombinedQueryResult, "编码规则");
    }
    private void exportSqlScript(List<Map<String, Object>> queryResult, String outputFileName) {
        List<String> sqlStatements = generateDynamicReplaceStatements(queryResult);

        // 创建文件夹
        File folder = new File("智能体厂");
        if (!folder.exists()) {
            folder.mkdirs();
        }

        // 写入文件
        String fileName = folder.getAbsolutePath() + File.separator + outputFileName + ".sql";
        try (PrintWriter writer = new PrintWriter(new FileWriter(fileName))) {
            for (String sqlStatement : sqlStatements) {
                writer.println(sqlStatement);
            }
            System.out.println("SQL脚本导出成功：" + fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private List<String> generateDynamicReplaceStatements(List<Map<String, Object>> queryResult) {
        List<String> sqlStatements = new ArrayList<>();

        for (Map<String, Object> result : queryResult) {
            sqlStatements.add(generateDynamicReplaceStatement(result));
        }

        return sqlStatements;
    }

    private String generateDynamicReplaceStatement(Map<String, Object> result) {
        // 根据查询结果动态生成列名和值
        StringBuilder columnsStringBuilder = new StringBuilder();
        StringBuilder valuesStringBuilder = new StringBuilder();
        DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String tableName = (String) result.get("itable_name");

        for (Map.Entry<String, Object> entry : result.entrySet()) {
            String columnName = entry.getKey();
            Object columnValue = entry.getValue();
            if (columnName.equals("creator")||columnName.equals("modifier")){
                columnValue="1648877357806776320";
            }

            if (!"itable_name".equals(columnName)) {
                columnsStringBuilder.append("`").append(columnName).append("`").append(", ");

                // 处理不同类型的值
                if (columnValue instanceof String) {
                    if (columnName.equals("expression")||columnName.equals("regular_expression")) {
                        valuesStringBuilder.append("'").append(escapeRegex(escapeSqlString((String) columnValue))).append("', ");
                    } else {
                        valuesStringBuilder.append("'").append(escapeSqlString((String) columnValue)).append("', ");
                    }
                } else if (columnValue instanceof LocalDateTime) {
                    valuesStringBuilder.append("'").append(((LocalDateTime) columnValue).format(dateFormatter)).append("', ");
                }
                else if (columnValue instanceof Date) {
                    valuesStringBuilder.append("'").append(columnValue).append("', ");
                }else {
                    valuesStringBuilder.append(columnValue).append(", ");
                }
            }
        }

        // 移除最后一个逗号和空格
        String columnsString = columnsStringBuilder.substring(0, columnsStringBuilder.length() - 2);
        String valuesString = valuesStringBuilder.substring(0, valuesStringBuilder.length() - 2);

        return String.format("REPLACE INTO `%s` (%s) VALUES (%s);",tableName, columnsString, valuesString);
    }
    private String escapeSqlString(String value) {
        // 处理多行 JSON 字符串，将其转换为单行
        String processedValue = value.replaceAll("\\s+", " ");

        // 对其他字符串值进行正常的 SQL 转义处理
        // ...

        return processedValue;
    }
    private String escapeRegex(String regex) {
        // 对正则表达式中的反斜杠进行双重转义
        String escapedRegex = regex.replace("\\", "\\\\");

        // 对正则表达式中的单引号进行转义
        escapedRegex = escapedRegex.replace("'", "''");

        return escapedRegex;
    }

}
